/**
 * Witontek.com.
 * Copyright (c) 2012-2017 All Rights Reserved.
 */
package com.witon.witontool.core.service.impl;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.witon.witontool.core.model.db.DbConfigRepository;
import com.witon.witontool.core.model.db.model.DbConfig;
import com.witon.witontool.core.service.DbCheckService;
import com.witon.witontool.web.controller.dbcheck.model.CheckResult;
import com.witon.witontool.web.controller.dbcheck.model.DbModel;
import com.witon.witontool.web.controller.dbcheck.model.Field;
import com.witon.witontool.web.controller.dbcheck.model.Table;

/**
 * 
 * @author Long
 * @version $Id: DbCheckServiceImpl.java, v 0.1 2017年6月30日 下午4:20:29  xiaolong.ma  Exp $
 */
@Service
public class DbCheckServiceImpl implements DbCheckService {
    private final Logger       logger      = LoggerFactory.getLogger(this.getClass());

    private String             driverClass = "org.postgresql.Driver";

    @Autowired
    private DbConfigRepository dbConfigRepository;

    /** 
     * @throws Exception 
     * @see com.witon.witontool.core.service.DbCheckService#dbCheck(long)
     */
    @Override
    public CheckResult dbCheck(long id) {
        logger.info("{}", id);
        CheckResult r = new CheckResult();
        DbConfig dbConfig = dbConfigRepository.getById(id);
        //获取dev的表list
        DbModel devDbModel = getDbModel(dbConfig.getDevUrl(), dbConfig.getDevUsername(),
            dbConfig.getDevPassword());
        //获取prod的表list
        DbModel prodDbModel = getDbModel(dbConfig.getProdUrl(), dbConfig.getProdUsername(),
            dbConfig.getProdPassword());

        if (devDbModel == null || prodDbModel == null) {
            r.setSuccess(false);
            return r;
        }
        List<Table> devTableList = devDbModel.getTableList();
        List<Table> prodTableList = prodDbModel.getTableList();

        List<String> devSeqList = devDbModel.getSeqList();
        List<String> prodSeqList = prodDbModel.getSeqList();

        //进行比对
        List<String> nameList = checkExistsTableList(devTableList, prodTableList);
        r.setTableNameList(nameList);
        //TODO seq
        List<String> seqList = checkSeqList(devSeqList, prodSeqList);
        r.setSeqList(seqList);

        List<Table> tableList = checkTableFieldList(devTableList, prodTableList);
        r.setTableList(tableList);

        r.setSuccess(true);
        ;
        return r;
    }

    List<Table> checkTableFieldList(List<Table> devList, List<Table> prodList) {
        List<Table> list = new ArrayList<Table>();
        for (Table dt : devList) {
            Table pt = getTable(prodList, dt.getName());
            if (pt != null) {
                Table t = checkTableField(dt, pt);
                if (t != null) {
                    t.setName(dt.getName());
                    list.add(t);
                }
            }
        }

        return list;
    }

    /**
     * 检查两张表字段是否一致
     * @param dev
     * @param prod
     * @return
     */
    Table checkTableField(Table dev, Table prod) {
        Table table = new Table();
        List<Field> fieldList = table.getFieldList();
        boolean isSame = true;
        for (Field devField : dev.getFieldList()) {
            Field prodField = getField(prod, devField.getName());
            if (prodField == null) {
                isSame = false;
                devField.setMemo("无");
                fieldList.add(devField);
            } else if ((!prodField.getType().equals(devField.getType()))
                       || (prodField.getLength() != devField.getLength())) {
                isSame = false;
                devField.setMemo(prodField.getType() + "," + prodField.getLength());
                fieldList.add(devField);
            }
        }

        if (isSame) {
            return null;
        } else {
            return table;
        }
    }

    Field getField(Table prod, String fieldName) {
        for (Field f : prod.getFieldList()) {
            if (f.getName().equals(fieldName)) {
                return f;
            }
        }
        return null;
    }

    List<String> checkExistsTableList(List<Table> devList, List<Table> prodList) {
        List<String> list = new ArrayList<String>();
        for (Table t : devList) {
            Table pt = getTable(prodList, t.getName());
            if (pt == null) {
                list.add(t.getName());
            }
        }
        return list;
    }

    List<String> checkSeqList(List<String> devList, List<String> prodList) {
        List<String> list = new ArrayList<String>();
        for (String s : devList) {
            if (!prodList.contains(s)) {
                list.add(s);
            }
        }
        return list;
    }

    Table getTable(List<Table> prodList, String name) {
        for (Table t : prodList) {
            if (t.getName().equals(name)) {
                return t;
            }
        }
        return null;
    }

    DbModel getDbModel(String url, String username, String password) {
        DbModel db = new DbModel();
        Connection conn;
        try {
            Class.forName(driverClass);
            conn = DriverManager.getConnection(url, username, password);
            if (!conn.isClosed())
                System.out.println("Succeeded connecting to the Database!");
            else
                System.out.println("connect failed");
            // 获取所有表名  
            // Statement statement = conn.createStatement();

            List<Table> list = getTables(conn);
            List<String> seqList = getSeqList(conn);
            db.setSeqList(seqList);
            db.setTableList(list);

            //conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return db;
    }

    public static String convertDatabaseCharsetType(String in, String type) {
        String dbUser;
        if (in != null) {
            if (type.equals("oracle")) {
                dbUser = in.toUpperCase();
            } else if (type.equals("postgresql")) {
                dbUser = "public";
            } else if (type.equals("mysql")) {
                dbUser = null;
            } else if (type.equals("mssqlserver")) {
                dbUser = null;
            } else if (type.equals("db2")) {
                dbUser = in.toUpperCase();
            } else {
                dbUser = in;
            }
        } else {
            dbUser = "public";
        }
        return dbUser;
    }

    private List<Table> getTables(Connection conn) throws SQLException {
        DatabaseMetaData dbMetData = conn.getMetaData();
        // mysql convertDatabaseCharsetType null  
        ResultSet rs = dbMetData.getTables(null, convertDatabaseCharsetType("root", "postgresql"),
            null, new String[] { "TABLE", "VIEW" });
        List<Table> list = new ArrayList<Table>();
        while (rs.next()) {
            if (rs.getString(4) != null && (rs.getString(4).equalsIgnoreCase("TABLE")
                                            || rs.getString(4).equalsIgnoreCase("VIEW"))) {
                String tableName = rs.getString(3).toLowerCase();
                Table table = new Table();
                table.setName(tableName);

                List<Field> fieldList = table.getFieldList();
                // 根据表名提前表里面信息：  
                ResultSet colRet = dbMetData.getColumns(null, "%", tableName, "%");
                while (colRet.next()) {
                    String columnName = colRet.getString("COLUMN_NAME");
                    String columnType = colRet.getString("TYPE_NAME");
                    int datasize = colRet.getInt("COLUMN_SIZE");
                    String remarks = colRet.getString("REMARKS");
                    //                    int digits = colRet.getInt("DECIMAL_DIGITS");
                    //                    int nullable = colRet.getInt("NULLABLE");
                    Field field = new Field();
                    field.setName(columnName);
                    field.setType(columnType);
                    field.setLength(datasize);
                    field.setRemarks(remarks);
                    fieldList.add(field);
                }
                //logger.info("table={}", table);
                //getPrimaryKeys 不知道为什么取不出来，里面sql是正确的。。
                //ResultSet pkRet = dbMetData.getPrimaryKeys(conn.getCatalog(), null, tableName);
                String pkSql = "SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM,   ct.relname AS TABLE_NAME, a.attname AS COLUMN_NAME,   (i.keys).n AS KEY_SEQ, ci.relname AS PK_NAME "
                               + "FROM pg_catalog.pg_class ct   JOIN pg_catalog.pg_attribute a ON (ct.oid = a.attrelid)   JOIN pg_catalog.pg_namespace n ON (ct.relnamespace = n.oid)  "
                               + " JOIN (SELECT i.indexrelid, i.indrelid, i.indisprimary,              information_schema._pg_expandarray(i.indkey) AS keys         FROM pg_catalog.pg_index i) i     ON (a.attnum = (i.keys).x AND a.attrelid = i.indrelid)   JOIN pg_catalog.pg_class ci ON (ci.oid = i.indexrelid) WHERE true  AND ct.relname = '"
                               + tableName
                               + "'  AND i.indisprimary  ORDER BY table_name, pk_name, key_seq";
                ResultSet pkRet = conn.createStatement().executeQuery(pkSql);
                while (pkRet.next()) {
                    String columnName = pkRet.getString("COLUMN_NAME");
                    setPk(fieldList, columnName);
                }

                list.add(table);
            }
        }

        return list;

    }

    private void setPk(List<Field> fieldList, String fieldName) {
        for (Field f : fieldList) {
            if (f.getName().equals(fieldName)) {
                f.setPk(true);
                return;
            }
        }
    }

    private List<String> getSeqList(Connection conn) throws SQLException {
        String sql = "SELECT c.relname FROM pg_class c WHERE c.relkind = 'S'";
        List<String> list = new ArrayList<String>();
        try {
            Statement stmt = conn.createStatement();//创建一个Statement语句对象
            ResultSet rs = stmt.executeQuery(sql);//执行sql语句
            while (rs.next()) {
                list.add(rs.getString("relname"));

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;

    }

    /** 
     * @see com.witon.witontool.core.service.DbCheckService#getTableList(long)
     */
    @Override
    public List<Table> getTableList(long id) {
        DbConfig dbConfig = dbConfigRepository.getById(id);
        //获取dev的表list
        DbModel devDbModel = getDbModel(dbConfig.getDevUrl(), dbConfig.getDevUsername(),
            dbConfig.getDevPassword());

        if (devDbModel != null) {
            return devDbModel.getTableList();
        } else {
            return null;
        }
    }

}
